//-----------------------------------------------------------------------
// <copyright file="SqlUserCreateTests.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
// </copyright>
// <author>Barry Dorrans</author>
// <email>barryd@charteris.com</email>
// <date>2004-06-03</date>
// <summary>Unit test cases for SqlServer CreateUser action.</summary>
//-----------------------------------------------------------------------

namespace Microsoft.Sdc.Tasks.Test
{
    using System;
    using System.IO;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using Microsoft.Sdc.Tasks.Configuration;
    using Microsoft.Sdc.Tasks;
    using Microsoft.Build.Framework;


    // We need these to create suitable users to add into SQL
    using Microsoft.Sdc.Tasks.Configuration.ActiveDirectory;
    using Microsoft.Sdc.Tasks.ActiveDirectory;

    // And we need these to impersonate users without adequate permissions in SQL to add people
    using System.Runtime.InteropServices;
    using System.Security.Principal;
    using System.Security.Permissions;

    // And we need these to check our results
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;

    /// <summary>
    /// Unit tests for Tasks.Sql.Access.Grant scenarios
    /// </summary>
    /// <remarks>
    /// Note that we're talking NT/AD users here, and it's not really creation it's adding
    /// them into the SQL Security setup.
    ///
    /// Assumptions :
    /// 
    /// In order for the tests to pass you will need to have SQL running locally, without an
    /// instance name.
    /// Checks have been targetted at SQL2000, of course when Yukon arrives who knows if the
    /// schema tables change.
    /// As we're using the ActiveDirectory.User.Create task to create suitable test users, 
    /// then clean them up again, I'm assuming that this code functions as expected.
    /// </remarks>
    [TestClass]
    public class SqlAccessGrantTests
    {       
        private string _testUserName = String.Empty;
        private string _testPassword = "1234$abcd";

        [TestInitialize]
        public void InitBuildEngineInterface()
        {
            this.CreateUserName();
        }

        /// <summary>
        /// Create a new, random(ish) local NT account for testing
        /// </summary>
        /// <remarks>
        /// Note that this relies on the ActiveDirectory.User.Create task functioning
        /// as expected.
        /// </remarks>
        private void CreateUserName()
        {
            Guid guid = Guid.NewGuid();
            string username = guid.ToString();
            username = username.Replace("-", "").Substring(0, 11);

            Console.WriteLine("Test Setup, creating test username " + username);

            if (User.Exists(username))
                Assert.Fail("Temporary user already exists.");
            // Now create the user, using the CreateActiveDirectoryUser task
            Tasks.ActiveDirectory.User.Create adTask = new Tasks.ActiveDirectory.User.Create();
            adTask.UserName = username;
            adTask.Password = _testPassword;
            bool taskReturnValue = adTask.Execute();
            Assert.IsTrue( taskReturnValue);
            // Check the user now exists
            bool userExists = User.Exists(username);
            Assert.IsTrue( userExists);

            _testUserName = username;
        }

        /// <summary>
        /// Clean up the previously created testing username
        /// </summary>
        /// <remarks>
        /// As this has the [TearDown] attribute, nunit will run it at the end of each test
        /// </remarks>
        [TestCleanup]
        public void CleanUserName()
        {
            Console.WriteLine("Test Teardown, deleting test username " + this._testUserName);
            User user = User.Load(this._testUserName);
            user.Delete();
        }

        public SqlAccessGrantTests()
        {

        }

        /// <summary>
        /// Test what happens when you call the execute method without setting parameters
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void TestExecuteWithNoParameters()
        {
            Tasks.Sql.Access.Grant task = new Tasks.Sql.Access.Grant();
           
            task.Execute();
        }

        /// <summary>
        /// Check the default properties after object create, and then the property set/get methods
        /// </summary>
        [TestMethod]
        public void TestProperties()
        {
            Tasks.Sql.Access.Grant task = new Tasks.Sql.Access.Grant();
           
            task.DatabaseName = "(dbname)";
            if (task.DatabaseName != "(dbname)")
                Assert.Fail("database name property set/get failed.");

            task.ServerName = "(servername)";
            if (task.ServerName != "(servername)")
                Assert.Fail("server name property set/get failed.");

            task.UserName = "(username)";
            if (task.UserName != "(username)")
                Assert.Fail("username property set/get failed.");
        }

        /// <summary>
        /// Create addition of user on the (local) SQL server, without a named instance
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestGrantAccessAgainstLocalServer()
        {
            bool taskReturnValue;

            Tasks.Sql.Access.Grant task = new Tasks.Sql.Access.Grant();
            // We need to munge the username into a full AD path in order to get SQL to
            // accept it
            task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
            taskReturnValue = task.Execute();
            Assert.IsTrue( taskReturnValue);

            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand userCommand = new SqlCommand(
                "SELECT 1 FROM sysusers WHERE name='" +
                System.Environment.MachineName + @"\" + this._testUserName + "'",
                dbConnection);
            SqlDataReader userReader = userCommand.ExecuteReader();
            if (!userReader.HasRows)
                Assert.Fail("Cannot find username in sysusers.");
            Console.WriteLine("User created and added");
            userReader.Close();
            userReader.Dispose();

            // Clean up the temporary user
            userCommand.CommandText = "EXEC sp_revokelogin '" +
                System.Environment.MachineName + @"\" + this._testUserName + "'";
            int rowsReturned = userCommand.ExecuteNonQuery();
            userCommand.Dispose();
            dbConnection.Close();
            dbConnection.Dispose();

        }

        /// <summary>
        /// Create addition of user on the (local) SQL server, by specifying the machine name
        /// as the server name to be used.
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestGrantAccessAgainstNamedServer()
        {
            bool taskReturnValue;

            Tasks.Sql.Access.Grant task = new Tasks.Sql.Access.Grant();
            // We need to munge the username into a full AD path in order to get SQL to
            // accept it
            task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
            task.ServerName = System.Environment.MachineName;
            taskReturnValue = task.Execute();
            Assert.IsTrue( taskReturnValue);

            SqlConnection dbConnection = new SqlConnection("Data Source=" + System.Environment.MachineName + ";" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand userCommand = new SqlCommand(
                "SELECT 1 FROM sysusers WHERE name='" +
                System.Environment.MachineName + @"\" + this._testUserName + "'",
                dbConnection);
            SqlDataReader userReader = userCommand.ExecuteReader();
            if (!userReader.HasRows)
                Assert.Fail("Cannot find username in sysusers.");
            Console.WriteLine("User created and added");
            userReader.Close();
            userReader.Dispose();

            // Clean up the temporary user
            userCommand.CommandText = "EXEC sp_revokelogin '" +
                System.Environment.MachineName + @"\" + this._testUserName + "'";
            int rowsReturned = userCommand.ExecuteNonQuery();
            userCommand.Dispose();
            dbConnection.Close();
            dbConnection.Dispose();
        }

        /// <summary>
        /// Test for failure when adding a non-existent user.
        /// </summary>
        /// <remarks>This is simply ran against (local)</remarks>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestGrantAccessWithNonExistentUser()
        {
            Guid guid = Guid.NewGuid();
            string username = guid.ToString();
            username = username.Replace("-", "").Substring(0, 11);
            Console.WriteLine("non-existent username test will use " + username);
            if (User.Exists(username))
                Assert.Fail("Temporary user already exists.");

            Tasks.Sql.Access.Grant task = new Tasks.Sql.Access.Grant();
            
            // We need to munge the username into a full AD path in order to get SQL to
            // accept it
            task.UserName = System.Environment.MachineName + @"\" + username;
            task.ServerName = System.Environment.MachineName;
            try
            {
                bool taskReturnValue = task.Execute();
                Assert.Fail("Addition of non-existent user did not fail.");
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine) task.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct user does not exist message
                Assert.IsTrue(lastMessage.IndexOf("Windows NT user or group '" +
                    task.UserName +
                    "' not found. Check the name again.") >= 0);
            }


        }
        /// <summary>
        /// Test for failure against a non-existent SQL server
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestGrantAccessAgainstBadServer()
        {
            // Generate a new, random-ish instance name
            Guid guid = Guid.NewGuid();
            string instanceName = guid.ToString();
            instanceName = System.Environment.MachineName + @"\" + instanceName.Replace("-", "").Substring(0, 11);

            // Now check the instance doesn't actually exist
            try
            {
                SqlConnection dbConnection = new SqlConnection("Data Source=" + instanceName + ";" +
                                "Initial Catalog=master;" +
                                "Integrated Security=SSPI;" +
                                "Connection Timeout=1");
                dbConnection.Open();
                dbConnection.Close();
                Assert.Fail("Random SQL instance name actually exists on this machine.");
            }
            catch (SqlException)
            {
                // Swallow 'em.
            }

            // Now lets try to add this user into the SQL users list
                Tasks.Sql.Access.Grant task = new Tasks.Sql.Access.Grant();
                
            try
            {
                // We need to munge the username into a full AD path in order to get SQL to
                // accept it
                task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
                task.ServerName = instanceName;
                task.ConnectionTimeout = 1;
                task.Execute();
                Assert.Fail("Adding a user to a non-existent instance did not fail.");
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine) task.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct user does not exist message
                Assert.IsTrue(lastMessage.IndexOf("Shared Memory Provider: The system cannot find the file specified.") >= 0);
            }

        }

        /// <summary>
        /// Test attempting to add a user when the security context we are running in does
        /// not have adequate permissions
        /// </summary>
        /// <remarks>
        /// </remarks>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        [SecurityPermission(SecurityAction.Demand, Flags =
           SecurityPermissionFlag.UnmanagedCode)]
        public void TestGrantAccessWithoutPermissions()
        {
            // First add the [Setup] created test user into SQL
            bool taskReturnValue;

            Tasks.Sql.Access.Grant task = new Tasks.Sql.Access.Grant();
            // We need to munge the username into a full AD path in order to get SQL to
            // accept it
            task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
            taskReturnValue = task.Execute();
            Assert.IsTrue(taskReturnValue,"User addition, with no role, failed.");
            // Now we create a second user to add
            Guid guid = Guid.NewGuid();
            string username = guid.ToString();
            username = username.Replace("-", "").Substring(0, 11);
            if (User.Exists(username))
                Assert.Fail("2nd Temporary user already exists.");
            // Now create the user, using the CreateActiveDirectoryUser task
            Tasks.ActiveDirectory.User.Create adTask = new Tasks.ActiveDirectory.User.Create();
            adTask.UserName = username;
            adTask.Password = _testPassword;
            taskReturnValue = adTask.Execute();
            Assert.IsTrue(taskReturnValue, "Cannot create 2nd temporary user.");
            // Check the user now exists
            bool userExists = User.Exists(username);
            Assert.IsTrue(userExists, "Temporary user 2nd existance check.");


            // Now switch context to this user
            const int LOGON32_LOGON_INTERACTIVE = 2;
            const int LOGON32_PROVIDER_DEFAULT = 0;
            IntPtr userHandle = new IntPtr(0);
            bool returnValue = NativeMethods.LogonUser(_testUserName,
                System.Environment.MachineName,
                _testPassword,
                LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref userHandle);

            if (!returnValue)
                throw new Exception("Invalid Username");
            WindowsIdentity newId = new WindowsIdentity(userHandle);
            WindowsImpersonationContext impersonatedUser = newId.Impersonate();
            Console.WriteLine("Now running in context of : " +
                            WindowsIdentity.GetCurrent().Name);
            // Now try it, using the previously constructed task, for neatness
             Tasks.Sql.Access.Grant contextTask = new Tasks.Sql.Access.Grant();
               
            try
            {               
                contextTask.UserName = System.Environment.MachineName + @"\" + username;
                task.Execute();
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine)contextTask.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct no permissions
                Assert.IsTrue(lastMessage.IndexOf("User does not have permission to perform this action.") >= 0);

            }
            finally
            {
                try
                {
                    impersonatedUser.Undo();
                    if (userHandle != IntPtr.Zero)
                        NativeMethods.CloseHandle(userHandle);
                }
                catch
                {
                    throw;
                }
            }

            User user = User.Load(username);
            taskReturnValue = user.Delete();

            // Clean up the temporary user
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand userCommand = new SqlCommand(
                "EXEC sp_revokelogin '" +
                System.Environment.MachineName + @"\" + this._testUserName + "'",
                dbConnection);
            userCommand.ExecuteNonQuery();
            userCommand.Dispose();
            dbConnection.Close();
            dbConnection.Dispose();

        }


        internal class NativeMethods
        {
            [DllImport("advapi32.dll", CharSet = CharSet.Auto)]
            public static extern bool LogonUser(String lpszUserName, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);
            [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
            public extern static bool CloseHandle(IntPtr handle);

        }
    }


}